/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package alwv.reports.pdf;

import alwv.database.DerbyConnection;
import alwv.database.Query;
import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Element;
import com.itextpdf.text.Font;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;
import java.awt.Desktop;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.math.RoundingMode;
import java.sql.Connection;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Brad
 */
public class MonthlyReport {

    private static final float LEFT = 0.25f;
    private static final float RIGHT = 0.25f;
    private static final float TOP = 50.0f;
    private static final float BOTTOM = 50.0f;
    private static final String tab = "    ";

    public static void createReport(Connection conn, String month, int year, Date date, String filename) {
        try {
            SimpleDateFormat formatter = new SimpleDateFormat("MM/dd/yyyy");
            String dateString = formatter.format(new java.util.Date(date.getTime()));

            Document d = new Document(PageSize.A4);
            d.setMargins(LEFT, RIGHT, TOP, BOTTOM);

            PdfWriter writer = PdfWriter.getInstance(d, new FileOutputStream(filename));
            d.open();

            Font f = new Font(Font.FontFamily.TIMES_ROMAN, 14, Font.BOLD);
            Paragraph top = new Paragraph("Treasurer's Report " + month + " " + year + " - " + (year+1), f);
            top.setAlignment(Element.ALIGN_CENTER);
            d.add(top);

            f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.BOLD);
            top = new Paragraph("Using budget for " + year + " - " + (year+1) + " fiscal year", f);
            top.setAlignment(Element.ALIGN_CENTER);
            top.setSpacingAfter(10f);
            d.add(top);

            PdfPTable table = new PdfPTable(6);
            float[] widths = {20f, 75f, 25f, 25f, 25f, 20f};
            //table.setLockedWidth(true);
            table.setWidths(widths);
            table.setWidthPercentage(85f);
            table.setHeaderRows(2);

            table.addCell(header1Cell(dateString));
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            //table.addCell(header1Cell("Page 1"));
            table.addCell(emptyCell(""));

            table.addCell(emptyCell(""));
            table.addCell(header2Cell("Category Description"));
            table.addCell(header2Cell("Actual as of " + dateString));
            table.addCell(header2Cell("Budget"));
            table.addCell(header2Cell("Difference"));
            table.addCell(emptyCell(""));

            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));

            // Income

            table.addCell(emptyCell(""));
            table.addCell(headingCell("Income"));
            table.addCell(headingCell(""));
            table.addCell(headingCell(""));
            table.addCell(headingCell(""));
            table.addCell(emptyCell(""));

            ArrayList<String> information = Query.getAllIncomeCategoriesAndSubcategories(conn);
            String currentCategory = information.get(0);

            BigDecimal totalCategory = new BigDecimal(0.0);
            totalCategory = totalCategory.setScale(2, BigDecimal.ROUND_HALF_EVEN);

            BigDecimal totalBudget = new BigDecimal(0.0);
            totalBudget = totalBudget.setScale(2, BigDecimal.ROUND_HALF_EVEN);

            BigDecimal overallTotal = new BigDecimal(0.0);
            overallTotal = overallTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);

            BigDecimal overallBudget = new BigDecimal(0.0);
            overallBudget = overallBudget.setScale(2, BigDecimal.ROUND_HALF_EVEN);

            for (int i = 0; i < information.size(); i++) {

                if (Query.isCategory(conn, information.get(i)) && !information.get(i).equals(currentCategory)) {
                    if (Query.hasSubcategory(conn, currentCategory)) {
                        table.addCell(emptyCell(""));

                        table.addCell(subCategoryTotalTitleCell("TOTAL " + currentCategory));

                        table.addCell(subCategoryTotalDollarCell(String.valueOf(totalCategory)));
                        table.addCell(subCategoryTotalDollarCell(String.valueOf(totalBudget)));
                        table.addCell(subCategoryTotalDollarCell(String.valueOf(totalCategory.subtract(totalBudget))));

                        totalCategory = new BigDecimal(0.0);
                        totalCategory = totalCategory.setScale(2, BigDecimal.ROUND_HALF_EVEN);
                        totalBudget = new BigDecimal(0.0);
                        totalBudget = totalBudget.setScale(2, BigDecimal.ROUND_HALF_EVEN);

                        table.addCell(emptyCell(""));
                    }
                    currentCategory = information.get(i);
                }
                if (!information.get(i).equals("-")) {
                    table.addCell(emptyCell(""));
                    if (Query.isSubcategory(conn, information.get(i))) {
                        table.addCell(subCategoryCell(information.get(i)));
                        // Put in totals
                        double asOfDate = Query.subcategoryIncomeActualValueAsOfDate(conn, information.get(i), year, date);
                        BigDecimal d1 = new BigDecimal(asOfDate);
                        d1 = d1.setScale(2, BigDecimal.ROUND_HALF_EVEN);
                        double budgetValue = Query.getSubcategoryBudgetValue(conn, information.get(i), year);
                        BigDecimal d2 = new BigDecimal(budgetValue);
                        d2 = d2.setScale(2, BigDecimal.ROUND_HALF_EVEN);

                        totalCategory = totalCategory.add(d1);
                        totalBudget = totalBudget.add(d2);

                        overallTotal = overallTotal.add(d1);
                        overallBudget = overallBudget.add(d2);

                        table.addCell(regAmountCell(String.valueOf(d1)));
                        table.addCell(regAmountCell(String.valueOf(d2)));
                        table.addCell(regAmountCell(String.valueOf(d1.subtract(d2))));


                    } else {
                        if (!Query.hasSubcategory(conn, currentCategory)) {
                            table.addCell(categoryCell(information.get(i)));
                            // Put in totals
                            double asOfDate = Query.categoryIncomeActualValueAsOfDate(conn, information.get(i), year, date);
                            BigDecimal d1 = new BigDecimal(asOfDate);
                            d1 = d1.setScale(2, BigDecimal.ROUND_HALF_EVEN);
                            double budgetValue = Query.getCategoryBudgetValue(conn, information.get(i), year);
                            BigDecimal d2 = new BigDecimal(budgetValue);
                            d2 = d2.setScale(2, BigDecimal.ROUND_HALF_EVEN);

                            overallTotal = overallTotal.add(d1);
                            overallBudget = overallBudget.add(d2);

                            table.addCell(regAmountCell(String.valueOf(d1)));
                            table.addCell(regAmountCell(String.valueOf(d2)));
                            table.addCell(regAmountCell(String.valueOf(d1.subtract(d2))));

                        } else {
                            table.addCell(categoryCell(information.get(i)));
                            table.addCell(emptyCell(""));
                            table.addCell(emptyCell(""));
                            table.addCell(emptyCell(""));
                        }
                    }
                    table.addCell(emptyCell(""));
                }
            }

            if (Query.hasSubcategory(conn, currentCategory)) {
                table.addCell(emptyCell(""));

                table.addCell(subCategoryTotalTitleCell("TOTAL " + currentCategory));
                table.addCell(subCategoryTotalDollarCell(String.valueOf(totalCategory)));
                table.addCell(subCategoryTotalDollarCell(String.valueOf(totalBudget)));
                table.addCell(subCategoryTotalDollarCell(String.valueOf(totalCategory.subtract(totalBudget))));

                table.addCell(emptyCell(""));
            }

            // Total For Income
            table.addCell(emptyCell(""));

            table.addCell(headingTotalTitleCell("TOTAL Income"));
            table.addCell(headingTotalDollarCell(String.valueOf(overallTotal)));
            table.addCell(headingTotalDollarCell(String.valueOf(overallBudget)));
            table.addCell(headingTotalDollarCell(String.valueOf(overallTotal.subtract(overallBudget))));

            BigDecimal totalIncome = new BigDecimal(overallTotal.doubleValue());
            totalIncome = totalIncome.setScale(2, BigDecimal.ROUND_HALF_EVEN);
            
            BigDecimal totalIncomeBudget = new BigDecimal(overallBudget.doubleValue());
            totalBudget = totalBudget.setScale(2, BigDecimal.ROUND_HALF_EVEN);
            
            table.addCell(emptyCell(""));

            // Empty Row
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));
            table.addCell(emptyCell(""));

            // Expenses

            table.addCell(emptyCell(""));
            table.addCell(headingCell("Expenses"));
            table.addCell(headingCell(""));
            table.addCell(headingCell(""));
            table.addCell(headingCell(""));
            table.addCell(emptyCell(""));

            totalCategory = new BigDecimal(0.0);
            totalCategory = totalCategory.setScale(2, BigDecimal.ROUND_HALF_EVEN);

            totalBudget = new BigDecimal(0.0);
            totalBudget = totalBudget.setScale(2, BigDecimal.ROUND_HALF_EVEN);

            overallTotal = new BigDecimal(0.0);
            overallTotal = overallTotal.setScale(2, BigDecimal.ROUND_HALF_EVEN);

            overallBudget = new BigDecimal(0.0);
            overallBudget = overallBudget.setScale(2, BigDecimal.ROUND_HALF_EVEN);

            information = Query.getAllExpenseCategoriesAndSubcategories(conn);
            currentCategory = information.get(0);
            for (int i = 0; i < information.size(); i++) {
                if (Query.isCategory(conn, information.get(i)) && !information.get(i).equals(currentCategory)) {
                    if (Query.hasSubcategory(conn, currentCategory)) {
                        table.addCell(emptyCell(""));

                        table.addCell(subCategoryTotalTitleCell("TOTAL " + currentCategory));
                        
                        table.addCell(subCategoryTotalDollarCell(String.valueOf(totalCategory.negate())));
                        table.addCell(subCategoryTotalDollarCell(String.valueOf(totalBudget.negate())));
                        table.addCell(subCategoryTotalDollarCell(String.valueOf(totalCategory.subtract(totalBudget).negate())));

                        totalCategory = new BigDecimal(0.0);
                        totalCategory = totalCategory.setScale(2, BigDecimal.ROUND_HALF_EVEN);
                        totalBudget = new BigDecimal(0.0);
                        totalBudget = totalBudget.setScale(2, BigDecimal.ROUND_HALF_EVEN);                        

                        table.addCell(emptyCell(""));
                    }
                    currentCategory = information.get(i);
                }
                if (!information.get(i).equals("-")) {
                    table.addCell(emptyCell(""));
                    if (Query.isSubcategory(conn, information.get(i))) {
                        table.addCell(subCategoryCell(information.get(i)));
                        // Put in totals
                        double asOfDate = Query.subcategoryExpenseActualValueAsOfDate(conn, information.get(i), year, date);
                        BigDecimal d1 = new BigDecimal(asOfDate);
                        d1 = d1.setScale(2, BigDecimal.ROUND_HALF_EVEN);
                        double budgetValue = Query.getSubcategoryBudgetValue(conn, information.get(i), year);
                        BigDecimal d2 = new BigDecimal(budgetValue);
                        d2 = d2.setScale(2, BigDecimal.ROUND_HALF_EVEN);

                        totalCategory = totalCategory.add(d1);
                        totalBudget = totalBudget.add(d2);

                        overallTotal = overallTotal.add(d1);
                        overallBudget = overallBudget.add(d2);

                        table.addCell(regAmountCell(String.valueOf(d1.negate())));
                        table.addCell(regAmountCell(String.valueOf(d2.negate())));
                        table.addCell(regAmountCell(String.valueOf(d1.subtract(d2).negate())));


                    } else {
                        if (!Query.hasSubcategory(conn, currentCategory)) {
                            table.addCell(categoryCell(information.get(i)));
                            // Put in totals
                            double asOfDate = Query.categoryExpenseActualValueAsOfDate(conn, information.get(i), year, date);
                            BigDecimal d1 = new BigDecimal(asOfDate);
                            d1 = d1.setScale(2, BigDecimal.ROUND_HALF_EVEN);
                            double budgetValue = Query.getCategoryBudgetValue(conn, information.get(i), year);
                            BigDecimal d2 = new BigDecimal(budgetValue);
                            d2 = d2.setScale(2, BigDecimal.ROUND_HALF_EVEN);

                            overallTotal = overallTotal.add(d1);
                            overallBudget = overallBudget.add(d2);

                            table.addCell(regAmountCell(String.valueOf(d1.negate())));
                            table.addCell(regAmountCell(String.valueOf(d2.negate())));
                            table.addCell(regAmountCell(String.valueOf(d1.subtract(d2).negate())));

                        } else {
                            System.out.println("ELSE");
                            table.addCell(categoryCell(information.get(i)));
                            table.addCell(emptyCell(""));
                            table.addCell(emptyCell(""));
                            table.addCell(emptyCell(""));
                        }
                    }
                    table.addCell(emptyCell(""));
                }
            }

            if (Query.hasSubcategory(conn, currentCategory)) {
                table.addCell(emptyCell(""));

                table.addCell(subCategoryTotalTitleCell("TOTAL " + currentCategory));
                
                table.addCell(subCategoryTotalDollarCell(String.valueOf(totalCategory.negate())));
                table.addCell(subCategoryTotalDollarCell(String.valueOf(totalBudget.negate())));
                table.addCell(subCategoryTotalDollarCell(String.valueOf(totalCategory.subtract(totalBudget).negate())));

                table.addCell(emptyCell(""));
            }

            // Total For Expenses
            table.addCell(emptyCell(""));

            table.addCell(headingTotalTitleCell("TOTAL Expenses"));
            table.addCell(headingTotalDollarCell(String.valueOf(overallTotal.negate())));
            table.addCell(headingTotalDollarCell(String.valueOf(overallBudget.negate())));
            table.addCell(headingTotalDollarCell(String.valueOf(overallTotal.subtract(overallBudget).negate())));

            table.addCell(emptyCell(""));

            // Overall Total
            table.addCell(emptyCell(""));

            table.addCell(overallTotalTitleCell("OVERALL TOTAL"));
            table.addCell(headingTotalDollarCell(String.valueOf(overallTotal.negate().add(totalIncome))));
            table.addCell(headingTotalDollarCell(String.valueOf(overallBudget.negate().add(totalIncomeBudget))));
            table.addCell(headingTotalDollarCell(String.valueOf(overallTotal.negate().add(totalIncome).subtract(overallBudget.negate().add(totalIncomeBudget)))));

            table.addCell(emptyCell(""));

            d.add(table);

            d.close();

            File file = new File(filename);
            Desktop.getDesktop().open(file);
        } catch (IOException ex) {
            Logger.getLogger(MonthlyReport.class.getName()).log(Level.SEVERE, null, ex);
        } catch (DocumentException ex) {
            Logger.getLogger(MonthlyReport.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private static PdfPCell emptyCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setBorder(0);
        return cell;
    }

    private static PdfPCell regAmountCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setHorizontalAlignment(Element.ALIGN_RIGHT);
        cell.setBorder(0);
        return cell;
    }

    private static PdfPCell overallTotalTitleCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.BOLD);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setBorder(0);
        cell.setBorderWidthTop(0.5f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_LEFT);
        return cell;
    }

    private static PdfPCell subCategoryTotalTitleCell(String s) {
        s = tab + tab + s;
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setBorder(0);
        cell.setBorderWidthTop(0.5f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_LEFT);
        return cell;
    }

    private static PdfPCell subCategoryTotalDollarCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setBorder(0);
        cell.setBorderWidthTop(0.5f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_RIGHT);
        return cell;
    }

    private static PdfPCell headingTotalTitleCell(String s) {
        s = tab + s;
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.BOLD);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setBorder(0);
        cell.setBorderWidthTop(0.5f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_LEFT);
        return cell;
    }

    private static PdfPCell headingTotalDollarCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.BOLD);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setBorder(0);
        cell.setBorderWidthTop(0.5f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_RIGHT);
        return cell;
    }

    private static PdfPCell headingCell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setBorder(0);
        cell.setBorderWidthBottom(0.5f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_LEFT);
        return cell;
    }

    private static PdfPCell categoryCell(String s) {
        s = tab + s;
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setBorder(0);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_LEFT);
        return cell;
    }

    private static PdfPCell subCategoryCell(String s) {
        s = tab + tab + s;
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setBorder(0);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_LEFT);
        return cell;
    }

    private static PdfPCell header1Cell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setNoWrap(false);
        cell.setBorder(0);
        cell.setFixedHeight(28f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        return cell;
    }

    private static PdfPCell header2Cell(String s) {
        Font f = new Font(Font.FontFamily.TIMES_ROMAN, 10, Font.NORMAL);
        PdfPCell cell = new PdfPCell(new Paragraph(s, f));
        cell.setNoWrap(false);
        cell.setBorder(0);
        cell.setBorderWidthBottom(0.5f);
        cell.setFixedHeight(28f);
        cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        return cell;
    }
}
